from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker,Session
from sqlalchemy.ext.declarative import declarative_base
import os
from dotenv import load_dotenv
from datetime import datetime, timedelta
from pathlib import Path


load_dotenv()

# 数据库配置
DB_USER = os.getenv("DB_USER", "root")
DB_PASSWORD = os.getenv("DB_PASSWORD", "kaikai66")
DB_HOST = os.getenv("DB_HOST", "localhost")
DB_PORT = os.getenv("DB_PORT", "3306")
DB_NAME = os.getenv("DB_NAME", "dev")

DATABASE_URL = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,
    pool_size=5,
    max_overflow=10,
    pool_recycle=3600,
    connect_args={"connect_timeout": 5}
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

from datetime import datetime, timedelta
from sqlalchemy.orm import Session

def delete_old_data(db: Session):
    """
    删除所有表中 DeadTime 超过5天的数据，并删除对应的图片文件
    """
    # 计算5天前的日期
    days_ago = datetime.utcnow() - timedelta(days=5)
    
    # 获取所有有 DeadTime 字段的模型，并排除抽象模型
    models_with_deadtime = []
    for model in Base.__subclasses__():
        if hasattr(model, 'DeadTime') and not getattr(model, '__abstract__', False):
            models_with_deadtime.append(model)
    
    # 创建图片存储目录的Path对象
    IMAGE_BASE_DIR = Path.cwd() / "alarm" / "images"
    
    # 遍历所有模型并删除旧数据
    for model in models_with_deadtime:
        try:
            # 查询需要删除的记录
            records_to_delete = db.query(model).filter(model.DeadTime < days_ago).all()
            
            if not records_to_delete:
                print(f"[跳过] 表 {model.__tablename__} 中没有旧数据")
                continue
                
            deleted_count = 0
            image_errors = 0
            images_deleted = 0
            
            # 检查模型是否有ImagePath字段
            has_image_path = hasattr(model, 'ImagePath')
            
            # 处理每条记录
            for record in records_to_delete:
                # 如果有ImagePath字段且值不为空
                if has_image_path and record.ImagePath:
                    try:
                        # 构建完整的图片路径
                        relative_path = record.ImagePath.replace('\\', os.sep)
                        image_path = IMAGE_BASE_DIR / relative_path
                        
                        # 安全验证：确保路径在基础目录内
                        if IMAGE_BASE_DIR.resolve() in image_path.resolve().parents:
                            if image_path.exists():
                                image_path.unlink()  # 删除图片文件
                                images_deleted += 1
                                print(f"已删除图片: {image_path}")
                            else:
                                print(f"警告: 图片不存在: {image_path}")
                        else:
                            print(f"安全警报: 路径 {image_path} 超出基础目录范围")
                            image_errors += 1
                    except Exception as img_e:
                        print(f"图片删除错误 {record.ImagePath}: {str(img_e)}")
                        image_errors += 1
                
                # 从数据库中删除记录
                db.delete(record)
                deleted_count += 1
            
            db.commit()
            if image_errors == 0:
                print(f"[成功] 表 {model.__tablename__} 删除完成: {deleted_count} 条记录, {images_deleted} 张图片")
            else:
                print(f"[警告] 表 {model.__tablename__} 删除完成: {deleted_count} 条记录, {images_deleted} 张图片, {image_errors} 个图片错误")
                  
        except Exception as e:
            db.rollback()
            print(f"[错误] 处理表 {model.__tablename__} 时出错: {str(e)}")